******** UPDATES ***************************************************
9/14/18: ADDED 2017
11/21/17: ADDED 2015 & 2016
10/28/15: ADDED 2014
10/14/14: ADDED 2013
9/11/13: ADDED 2012
11/15/12: CREATED INITIAL PROGRAM
*****************************************************************;

LIBNAME alldat '~/jim/data';


FILENAME mor88 pipe 'gunzip -c ~/jim/data/ce_debt/1988/mor.txt.gz';
FILENAME mor89 pipe 'gunzip -c ~/jim/data/ce_debt/1989/mor.txt.gz';
FILENAME mor90 pipe 'gunzip -c ~/jim/data/ce_debt/1990/mor.txt.gz';
FILENAME mor91 pipe 'gunzip -c ~/jim/data/ce_debt/1991/mor.txt.gz';
FILENAME mor92 pipe 'gunzip -c ~/jim/data/ce_debt/1992/mor.txt.gz';
FILENAME mor93 pipe 'gunzip -c ~/jim/data/ce_debt/1993/mor.txt.gz';
FILENAME mor94 pipe 'gunzip -c ~/jim/data/ce_debt/1994/mor.txt.gz';
FILENAME mor95 pipe 'gunzip -c ~/jim/data/ce_debt/1995/mor.txt.gz';
FILENAME mor96 pipe 'gunzip -c ~/jim/data/ce_debt/1996/mor.txt.gz';
FILENAME mor97 pipe 'gunzip -c ~/jim/data/ce_debt/1997/mor.txt.gz';
FILENAME mor98 pipe 'gunzip -c ~/jim/data/ce_debt/1998/mor.txt.gz';
FILENAME mor99 pipe 'gunzip -c ~/jim/data/ce_debt/1999/mor.txt.gz';
FILENAME mor100 pipe 'gunzip -c ~/jim/data/ce_debt/2000/mor.txt.gz';
FILENAME mor101 pipe 'gunzip -c ~/jim/data/ce_debt/2001/mor.txt.gz';
FILENAME mor102 pipe 'gunzip -c ~/jim/data/ce_debt/2002/mor.txt.gz';
FILENAME mor103 pipe 'gunzip -c ~/jim/data/ce_debt/2003/mor.txt.gz';
FILENAME mor104 pipe 'gunzip -c ~/jim/data/ce_debt/2004/mor.txt.gz';
FILENAME mor105 pipe 'gunzip -c ~/jim/data/ce_debt/2005/mor.txt.gz';
FILENAME mor106 pipe 'gunzip -c ~/jim/data/ce_debt/2006/mor.txt.gz';
FILENAME mor107 pipe 'gunzip -c ~/jim/data/ce_debt/2007/mor.txt.gz';
FILENAME mor108 pipe 'gunzip -c ~/jim/data/ce_debt/2008/mor.txt.gz';
FILENAME mor109 pipe 'gunzip -c ~/jim/data/ce_debt/2009/mor.txt.gz';
FILENAME mor110 pipe 'gunzip -c ~/jim/data/ce_debt/2010/mor.txt.gz';


OPTIONS obs=max;


***************************************************************************
MACROS FOR 1988-MOST RECENT YEAR, MORTGAGE PRINCIPLE
***************************************************************************;
* FOR SURVEY YEARS 1988-2010;
%MACRO read(yr);
 
  DATA mortgage&yr; 
   INFILE mor&yr; 
   
   IF 88<=&yr<=90 THEN DO;
     INPUT qyear 1-3 newid 4-11 PRNCPLX 63-68; 
   END;  
   IF 91<=&yr<=91 THEN DO;
     INPUT qyear 1-3 newid 4-11 PRNCPLX 63-68 QBLNCM1X	180-187	QBLNCM2X	189-196	QBLNCM3X	198-205; 
   END;  
   IF 92<=&yr<=95 THEN DO;
     INPUT qyear 1-3 newid 4-11 QBLNCM1X	157-164	QBLNCM2X	166-173	QBLNCM3X	175-182; 
   END;  
   IF 96<=&yr<=111 THEN DO;
     INPUT qyear 1-5 newid 6-13 QBLNCM1X	165-172	QBLNCM2X	174-181	QBLNCM3X	183-190; 
   END;  
      
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR 1995 AND 2004 B/C THATS A CHANGE-IN-SAMPLE YEAR; 
  IF FLOOR(qyear/10) NE &yr AND 88 LE &yr LE 94 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 96 LE &yr LE 103 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 110 THEN DELETE;

  IF 881 LE qyear LE 911 THEN principal=PRNCPLX;
     ELSE IF qyear GE 912 THEN principal=QBLNCM3X;

IF principal GT 0;
RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR principal;
   OUTPUT OUT=mort&yr SUM=;
RUN;
PROC MEANS; 
RUN; 


%MEND read;

********************************************************************;
* FOR SURVEY YEARS 2011 AND AFTER;
********************************************************************;
%MACRO read2(yr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_debt/&yr4/mor&ext..csv"
     OUT=mortgage&yr
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
  DATA mortgage&yr; 
   SET mortgage&yr(RENAME=(qyear=temp1 newid=temp2 QBLNCM3X=principal)); 
   
   qyear=temp1*1;
   newid=temp2*1;    
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR THE FINAL YEAR OF DATA; 
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 113 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 115 LE &yr LT 117 THEN DELETE;

KEEP qyear newid QBLNCM1X	QBLNCM2X principal year;
IF principal GT 0;

RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR principal;
   OUTPUT OUT=mort&yr SUM=;
RUN;
PROC MEANS; 
RUN; 



%MEND read2;



%read(88);
%read(89);
%read(90);
%read(91);
%read(92);
%read(93);
%read(94);
%read(95);
%read(96);
%read(97);
%read(98);
%read(99);
%read(100);
%read(101);
%read(102);
%read(103);
%read(104);
%read(105);
%read(106);
%read(107);
%read(108);
%read(109);
%read(110);

%read2(111,,2011);
%read2(112,12,2012);
%read2(113,13,2013);
%read2(114,14,2014);
%read2(115,15,2015);
%read2(116,16,2016);
%read2(117,17,2017);

******* DELETE OBS IN 955 THAT ARE ALSO IN 961 ****;
PROC SORT DATA=mort95;
   BY newid;
RUN;
PROC SORT DATA=mort96;
   BY newid;
RUN;

DATA temp;
  SET mort96;
  BY newid;
  IF first.newid;
RUN;

DATA mort95; 
  MERGE mort95(IN=in1) temp(IN=in2);
  BY newid; 
  if in2 THEN dat2=1;
  else dat2=0;
  IF in1;
  IF dat2=1 THEN DELETE;
RUN; 


******* DELETE OBS IN 045 THAT ARE ALSO IN 051 ****;
PROC SORT DATA=mort104;
   BY newid;
RUN;

PROC SORT DATA=mort105;
   BY newid;
RUN;

DATA temp;
SET mort105;
BY newid;
IF first.newid;
RUN;
DATA mort104; 
MERGE mort104(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN; 







******* DELETE OBS IN 145 THAT ARE ALSO IN 151 ****;
PROC SORT DATA=mort114;
   BY newid;
RUN;

PROC SORT DATA=mort115;
   BY newid;
RUN;

DATA temp;
SET mort115;
BY newid;
IF first.newid;
RUN;
DATA mort114; 
MERGE mort114(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN; 



********************************************************************
Stack all the data 
********************************************************************;

DATA alldat.mortgage8817;
SET mort88 mort89
    mort90 mort91 mort92 mort93 mort94 mort95 mort96 
    mort97 mort98 mort99 mort100 mort101 mort102 mort103 
    mort104 mort105 mort106 mort107 mort108 mort109 mort110 
    mort111 mort112 mort113 mort114 mort115 mort116 mort117;
    
 IF principal=. THEN principal=0;

RUN;

PROC MEANS DATA=alldat.mortgage8817;
RUN;
PROC SORT DATA=alldat.mortgage8817;
BY qyear;
PROC MEANS DATA=alldat.mortgage8817;
BY qyear;
RUN;

PROC SORT DATA=alldat.mortgage8817;
  BY newid;
RUN;
PROC SORT DATA=alldat.mortgage8816;
  BY newid;
RUN;
PROC COMPARE BASE=alldat.mortgage8817
	     COMPARE=alldat.mortgage8816;
   WHERE qyear LE 1164;
RUN;

******************************************************************************
******************************************************************************

HOME EQUITY LOANS

******************************************************************************
******************************************************************************;

FILENAME hel91 pipe 'gunzip -c ~/jim/data/ce_debt/1991/hel.txt.gz';
FILENAME hel92 pipe 'gunzip -c ~/jim/data/ce_debt/1992/hel.txt.gz';
FILENAME hel93 pipe 'gunzip -c ~/jim/data/ce_debt/1993/hel.txt.gz';
FILENAME hel94 pipe 'gunzip -c ~/jim/data/ce_debt/1994/hel.txt.gz';
FILENAME hel95 pipe 'gunzip -c ~/jim/data/ce_debt/1995/hel.txt.gz';
FILENAME hel96 pipe 'gunzip -c ~/jim/data/ce_debt/1996/hel.txt.gz';
FILENAME hel97 pipe 'gunzip -c ~/jim/data/ce_debt/1997/hel.txt.gz';
FILENAME hel98 pipe 'gunzip -c ~/jim/data/ce_debt/1998/hel.txt.gz';
FILENAME hel99 pipe 'gunzip -c ~/jim/data/ce_debt/1999/hel.txt.gz';
FILENAME hel100 pipe 'gunzip -c ~/jim/data/ce_debt/2000/hel.txt.gz';
FILENAME hel101 pipe 'gunzip -c ~/jim/data/ce_debt/2001/hel.txt.gz';
FILENAME hel102 pipe 'gunzip -c ~/jim/data/ce_debt/2002/hel.txt.gz';
FILENAME hel103 pipe 'gunzip -c ~/jim/data/ce_debt/2003/hel.txt.gz';
FILENAME hel104 pipe 'gunzip -c ~/jim/data/ce_debt/2004/hel.txt.gz';
FILENAME hel105 pipe 'gunzip -c ~/jim/data/ce_debt/2005/hel.txt.gz';
FILENAME hel106 pipe 'gunzip -c ~/jim/data/ce_debt/2006/hel.txt.gz';
FILENAME hel107 pipe 'gunzip -c ~/jim/data/ce_debt/2007/hel.txt.gz';
FILENAME hel108 pipe 'gunzip -c ~/jim/data/ce_debt/2008/hel.txt.gz';
FILENAME hel109 pipe 'gunzip -c ~/jim/data/ce_debt/2009/hel.txt.gz';
FILENAME hel110 pipe 'gunzip -c ~/jim/data/ce_debt/2010/hel.txt.gz';

OPTIONS obs=max;


***************************************************************************
MACROS FOR 1988-MOST RECENT YEAR, HOME EQ LOAN PRINCIPLE
***************************************************************************;
* FOR SURVEY YEARS 1988-2010;
%MACRO read3(yr);
 
  DATA home_eq&yr; 
   INFILE hel&yr; 
   
   IF 91<=&yr<=91 THEN DO;
     INPUT qyear 1-3 newid 4-11 QBLNCM1G	180-187	QBLNCM2G	189-196	QBLNCM3G	198-205; 
   END;  
   IF 92<=&yr<=95 THEN DO;
     INPUT qyear 1-3 newid 4-11 QBLNCM1G	157-164	QBLNCM2G	166-173	QBLNCM3G	175-182; 
   END;  
   IF 96<=&yr<=111 THEN DO;
     INPUT qyear 1-5 newid 6-13 QBLNCM1G	165-172	QBLNCM2G	174-181	QBLNCM3G	183-190; 
   END;  
      
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR 1995 AND 2004 B/C THATS A CHANGE-IN-SAMPLE YEAR; 
  IF FLOOR(qyear/10) NE &yr AND 88 LE &yr LE 94 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 96 LE &yr LE 103 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 110 THEN DELETE;

 IF qblncm3g GT 0;
RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR qblncm3g;
   OUTPUT OUT=heloan&yr SUM=prin_hel;
RUN;
PROC MEANS; 
RUN; 


%MEND read3;

************************************************************;
* FOR SURVEY YEARS 2011 AND AFTER;
************************************************************;
%MACRO read4(yr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_debt/&yr4/hel&ext..csv"
     OUT=home_eq&yr
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
  DATA home_eq&yr; 
   SET home_eq&yr(RENAME=(qyear=temp1 newid=temp2)); 
      
   qyear=temp1*1; 
   newid=temp2*1;    
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR THE FINAL YEAR OF DATA; 
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 113 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 115 LE &yr LT 117 THEN DELETE;

KEEP qyear newid QBLNCM1G	QBLNCM2G	QBLNCM3G year;
IF qblncm3g GT 0;

RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR qblncm3g;
   OUTPUT OUT=heloan&yr SUM=prin_hel;
RUN;
PROC MEANS; 
RUN; 



%MEND read4;


%read3(91);
%read3(92);
%read3(93);
%read3(94);
%read3(95);
%read3(96);
%read3(97);
%read3(98);
%read3(99);
%read3(100);
%read3(101);
%read3(102);
%read3(103);
%read3(104);
%read3(105);
%read3(106);
%read3(107);
%read3(108);
%read3(109);
%read3(110);

%read4(111,,2011);
%read4(112,12,2012);
%read4(113,13,2013);
%read4(114,14,2014);
%read4(115,15,2015);
%read4(116,16,2016);
%read4(117,17,2017);


******* DELETE OBS IN 955 THAT ARE ALSO IN 961 ****;
PROC SORT DATA=heloan95;
   BY newid;
RUN;
PROC SORT DATA=heloan96;
   BY newid;
RUN;

DATA temp;
  SET heloan96;
  BY newid;
  IF first.newid;
RUN;

DATA heloan95; 
  MERGE heloan95(IN=in1) temp(IN=in2);
  BY newid; 
  if in2 THEN dat2=1;
  else dat2=0;
  IF in1;
  IF dat2=1 THEN DELETE;
RUN; 


******* DELETE OBS IN 045 THAT ARE ALSO IN 051 ****;
PROC SORT DATA=heloan104;
   BY newid;
RUN;

PROC SORT DATA=heloan105;
   BY newid;
RUN;

DATA temp;
SET heloan105;
BY newid;
IF first.newid;
RUN;
DATA heloan104; 
MERGE heloan104(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN; 

******* DELETE OBS IN 145 THAT ARE ALSO IN 151 ****;
PROC SORT DATA=heloan114;
   BY newid;
RUN;

PROC SORT DATA=heloan115;
   BY newid;
RUN;

DATA temp;
SET heloan115;
BY newid;
IF first.newid;
RUN;
DATA heloan114; 
MERGE heloan114(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN; 

********************************************************************
Stack all the data 
********************************************************************;

DATA alldat.home_eq9117;
SET heloan91 heloan92 heloan93 heloan94 heloan95 heloan96 
    heloan97 heloan98 heloan99 heloan100 heloan101 heloan102 heloan103 
    heloan104 heloan105 heloan106 heloan107 heloan108 heloan109 heloan110 
    heloan111 heloan112 heloan113 heloan114 heloan115 heloan116 heloan117;
    
 IF prin_hel=. THEN prin_hel=0;

RUN;

PROC MEANS DATA=alldat.home_eq9117;
RUN;
PROC SORT DATA=alldat.home_eq9117;
BY qyear;
PROC MEANS DATA=alldat.home_eq9117;
BY qyear;
RUN;

PROC SORT DATA=alldat.home_eq9117;
  BY newid;
RUN;
PROC SORT DATA=alldat.home_eq9116;
  BY newid;
RUN;
PROC COMPARE BASE=alldat.home_eq9117
	     COMPARE=alldat.home_eq9116;
   WHERE qyear LE 1164;
RUN;

******************************************************************************
******************************************************************************

HOME EQUITY LINES OF CREDIT

******************************************************************************
******************************************************************************;

FILENAME oph91 pipe 'gunzip -c ~/jim/data/ce_debt/1991/oph.txt.gz';
FILENAME oph92 pipe 'gunzip -c ~/jim/data/ce_debt/1992/oph.txt.gz';
FILENAME oph93 pipe 'gunzip -c ~/jim/data/ce_debt/1993/oph.txt.gz';
FILENAME oph94 pipe 'gunzip -c ~/jim/data/ce_debt/1994/oph.txt.gz';
FILENAME oph95 pipe 'gunzip -c ~/jim/data/ce_debt/1995/oph.txt.gz';
FILENAME oph96 pipe 'gunzip -c ~/jim/data/ce_debt/1996/oph.txt.gz';
FILENAME oph97 pipe 'gunzip -c ~/jim/data/ce_debt/1997/oph.txt.gz';
FILENAME oph98 pipe 'gunzip -c ~/jim/data/ce_debt/1998/oph.txt.gz';
FILENAME oph99 pipe 'gunzip -c ~/jim/data/ce_debt/1999/oph.txt.gz';
FILENAME oph100 pipe 'gunzip -c ~/jim/data/ce_debt/2000/oph.txt.gz';
FILENAME oph101 pipe 'gunzip -c ~/jim/data/ce_debt/2001/oph.txt.gz';
FILENAME oph102 pipe 'gunzip -c ~/jim/data/ce_debt/2002/oph.txt.gz';
FILENAME oph103 pipe 'gunzip -c ~/jim/data/ce_debt/2003/oph.txt.gz';
FILENAME oph104 pipe 'gunzip -c ~/jim/data/ce_debt/2004/oph.txt.gz';
FILENAME oph105 pipe 'gunzip -c ~/jim/data/ce_debt/2005/oph.txt.gz';
FILENAME oph106 pipe 'gunzip -c ~/jim/data/ce_debt/2006/oph.txt.gz';
FILENAME oph107 pipe 'gunzip -c ~/jim/data/ce_debt/2007/oph.txt.gz';
FILENAME oph108 pipe 'gunzip -c ~/jim/data/ce_debt/2008/oph.txt.gz';
FILENAME oph109 pipe 'gunzip -c ~/jim/data/ce_debt/2009/oph.txt.gz';
FILENAME oph110 pipe 'gunzip -c ~/jim/data/ce_debt/2010/oph.txt.gz';


OPTIONS obs=max;


***************************************************************************
MACROS FOR 1988-MOST RECENT YEAR, HOME EQ LOC PRINCIPLE
***************************************************************************;
* FOR SURVEY YEARS 1988-2010;
%MACRO read5(yr);
 
  DATA home_eq&yr; 
   INFILE oph&yr; 
   
   IF 91<=&yr<=91 THEN DO;
     INPUT qyear 1-3 newid 4-11 PRINAMTX	28-35; 
   END;  
   IF 92<=&yr<=95 THEN DO;
     INPUT qyear 1-3 newid 4-11 PRINAMTX	28-35; 
   END;  
   IF 96<=&yr<=104 THEN DO;
     INPUT qyear 1-5 newid 6-13 PRINAMTX	30-37; 
   END;  
   IF 105<=&yr<=111 THEN DO;
     INPUT qyear 1-5 newid 6-13 PRINAMTX	67-74; 
   END;  
         
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR 1995 AND 2004 B/C THATS A CHANGE-IN-SAMPLE YEAR; 
  IF FLOOR(qyear/10) NE &yr AND 88 LE &yr LE 94 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 96 LE &yr LE 103 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 110 THEN DELETE;

 IF prinamtx GT 0;
RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR prinamtx;
   OUTPUT OUT=loc&yr SUM=prin_loc;
RUN;
PROC MEANS; 
RUN; 


%MEND read5;

************************************************************;
* FOR SURVEY YEARS 2011 AND AFTER;
************************************************************;
%MACRO read6(yr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_debt/&yr4/oph&ext..csv"
     OUT=home_eq&yr
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
  DATA home_eq&yr; 
   SET home_eq&yr(RENAME=(qyear=temp1 newid=temp2 TOTOWED=prinamtx)); 
      
   qyear=temp1*1; 
   newid=temp2*1;   
   year=1900+&yr;
   
* DELETE OBS FOR FOLLOWING YEAR, BUT KEEP FOR THE FINAL YEAR OF DATA; 
  IF FLOOR(qyear/10) NE 1900+&yr AND 105 LE &yr LE 113 THEN DELETE;
  IF FLOOR(qyear/10) NE 1900+&yr AND 115 LE &yr LT 117 THEN DELETE;


KEEP qyear newid prinamtx year;
IF prinamtx GT 0;

RUN;

PROC MEANS; 
RUN; 

PROC SORT;
   BY qyear newid;
RUN;
PROC MEANS SUM NOPRINT;
   BY qyear newid;
   VAR prinamtx;
   OUTPUT OUT=loc&yr SUM=prin_loc;
RUN;
PROC MEANS; 
RUN; 



%MEND read6;


%read5(91);
%read5(92);
%read5(93);
%read5(94);
%read5(95);
%read5(96);
%read5(97);
%read5(98);
%read5(99);
%read5(100);
%read5(101);
%read5(102);
%read5(103);
%read5(104);
%read5(105);
%read5(106);
%read5(107);
%read5(108);
%read5(109);
%read5(110);

%read6(111,,2011);
%read6(112,12,2012);
%read6(113,13,2013);
%read6(114,14,2014);
%read6(115,15,2015);
%read6(116,16,2016);
%read6(117,17,2017);

******* DELETE OBS IN 955 THAT ARE ALSO IN 961 ****;
PROC SORT DATA=loc95;
   BY newid;
RUN;
PROC SORT DATA=loc96;
   BY newid;
RUN;

DATA temp;
  SET loc96;
  BY newid;
  IF first.newid;
RUN;

DATA loc95; 
  MERGE loc95(IN=in1) temp(IN=in2);
  BY newid; 
  if in2 THEN dat2=1;
  else dat2=0;
  IF in1;
  IF dat2=1 THEN DELETE;
RUN; 


******* DELETE OBS IN 045 THAT ARE ALSO IN 051 ****;
PROC SORT DATA=loc104;
   BY newid;
RUN;

PROC SORT DATA=loc105;
   BY newid;
RUN;

DATA temp;
SET loc105;
BY newid;
IF first.newid;
RUN;
DATA loc104; 
MERGE loc104(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN; 


******* DELETE OBS IN 145 THAT ARE ALSO IN 151 ****;
PROC SORT DATA=loc114;
   BY newid;
RUN;

PROC SORT DATA=loc115;
   BY newid;
RUN;

DATA temp;
SET loc115;
BY newid;
IF first.newid;
RUN;
DATA loc114; 
MERGE loc114(IN=in1) temp(IN=in2);
BY newid; 
if in2 THEN dat2=1;
else dat2=0;
IF in1;
IF dat2=1 THEN DELETE;
RUN;


********************************************************************
Stack all the data 
********************************************************************;

DATA alldat.heloc9117;
SET loc91 loc92 loc93 loc94 loc95 loc96 
    loc97 loc98 loc99 loc100 loc101 loc102 loc103 
    loc104 loc105 loc106 loc107 loc108 loc109 loc110 loc111 
    loc112 loc113 loc114 loc115 loc116 loc117; 
    
 IF prin_loc=. THEN prin_loc=0;

RUN;

PROC MEANS DATA=alldat.heloc9117;
RUN;
PROC SORT DATA=alldat.heloc9117;
BY qyear;
PROC MEANS DATA=alldat.heloc9117;
BY qyear;
RUN;

PROC SORT DATA=alldat.heloc9117;
  BY newid;
RUN;
PROC SORT DATA=alldat.heloc9116;
  BY newid;
RUN;
PROC COMPARE BASE=alldat.heloc9117
	     COMPARE=alldat.heloc9116;
   WHERE qyear LE 1164;
RUN;
